package com.cn.wanxi.dao.impl;

import com.cn.wanxi.dao.UserDao;
import com.cn.wanxi.model.UserModel;
import com.cn.wanxi.util.JDBC;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

public class UserImpl implements UserDao {
    @Override
    public UserModel findByUserName(String username) {
//        1.封装sql语句
//        2.执行数据库--JDBC自己写的  连接数据库的方式 --
//        3.返回数据
        String sql = "select  * from  user where username='" + username + "'";
        ResultSet query = JDBC.query(sql);//得到数据库查询出来的结果集，需要封装到UserModel
        UserModel userModel = null;
        try {
            while (query.next()) {
                userModel = new UserModel();
//                userModel.setUsername(query.getString("username"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return userModel;
    }

    @Override
    public int add(UserModel userModel) {
//        SimpleDateFormat//Java的时间格式
        String sql = "" +
                "INSERT INTO `user` " +
                "(`username`, " +
                "`password`, " +
                "`phone`, " +
                "`sex`, " +
                "`birthday`, " +
                "`hobby`, " +
                " `update_time`, " +
                "`create_time`, " +
                "`enable`, " +
                "`email`)" +
                " VALUES (" +
                " '" + userModel.getUsername() + "', " +
                "'" + userModel.getPassword() + "'," +
                " '" + userModel.getPhone() + "', " +
                "'" + userModel.getSex() + "', " +
                "'" + userModel.getBirthday() + "', " +
                "'" + userModel.getHobby() + "', " +
                "now(), " +
                "now(), " +
                "1, " +
                "'" + userModel.getEmail() + "');";
        return JDBC.update(sql);
    }

    /**
     * 根据用户名和密码判断当前查询的数据是否有值
     * 1.如果有则进入while循环，直接返回一个对象，只不过里面为空值  null
     *
     * @param userModel
     * @return
     */
    @Override
    public UserModel findByUsernameAndPassword(UserModel userModel) {
        String sql = "select * from user where username='" + userModel.getUsername() + "' and password='" + userModel.getPassword() + "'";
        ResultSet resultSet = JDBC.query(sql);
        try {
            while (resultSet.next()) {
                return new UserModel();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<UserModel> findAll(UserModel userModel) {
        String sql = "select * from user where  true";
        if (!"".equals(userModel.getUsername()) && userModel.getUsername() != null)
            sql += " and username like '%" + userModel.getUsername() + "%'";
        if (!"".equals(userModel.getPhone()) && userModel.getPhone() != null)
            sql += " and  phone like '%" + userModel.getPhone() + "%'";
        if (!"".equals(userModel.getEmail()) && userModel.getEmail() != null)
            sql += " and  email like '%" + userModel.getEmail() + "%'";
        if (!"全部".equals(userModel.getSex()) && userModel.getSex() != null)
            sql += " and sex = '" + userModel.getSex() + "'";
        if (!"".equals(userModel.getHobby()) && userModel.getHobby() != null) {
            String aa[] = userModel.getHobby().split(",");
            sql += " and (";
            for (int i = 0; i < aa.length; i++) {
                if (i == 0) {
                    sql += "   find_in_set('" + aa[i] + "',hobby)";
                } else {
                    sql += " or  find_in_set('" + aa[i] + "',hobby)";
                }
            }
            sql += ")";
        }
        if (!"".equals(userModel.getStartBirthday()) && "".equals(userModel.getEndBirthday())) {
            sql += " and birthday >='" + userModel.getStartBirthday() + "'";
        }
        if ("".equals(userModel.getStartBirthday()) && !"".equals(userModel.getEndBirthday())) {
            sql += " and birthday <='" + userModel.getEndBirthday() + "'";
        }
        if (!"".equals(userModel.getStartBirthday()) && !"".equals(userModel.getEndBirthday())) {
            sql += " and birthday between '" + userModel.getStartBirthday() + "' and '" + userModel.getEndBirthday() + "'";
        }

        sql += " order by update_time desc ";
//        分页  -- 对象
        sql += " limit  " + (userModel.getPageModel().getCurPage() - 1) * userModel.getPageModel().getPageSize() + "," + userModel.getPageModel().getPageSize();
        ResultSet resultSet = JDBC.query(sql);
        List<UserModel> list = new ArrayList<>();
        try {
            while (resultSet.next()) {
                UserModel model = new UserModel();
                model.setId(resultSet.getInt("id"));
                model.setUsername(resultSet.getString("username"));
                model.setEmail(resultSet.getString("email"));
                model.setPhone(resultSet.getString("phone"));
                model.setSex(resultSet.getString("sex"));
                model.setHobby(resultSet.getString("hobby"));
                model.setBirthday(resultSet.getString("birthday"));
                model.setEnable(resultSet.getInt("enable"));
                list.add(model);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public int del(Integer id) {
        String sql = "delete from user where id=" + id;
        return JDBC.update(sql);
    }

    @Override
    public UserModel findById(Integer id) {
        String sql = "select * from user where id=" + id;
        UserModel model = null;
        ResultSet resultSet = JDBC.query(sql);
        try {
            while (resultSet.next()) {
                model = new UserModel();
                model.setId(resultSet.getInt("id"));
                model.setUsername(resultSet.getString("username"));
                model.setEmail(resultSet.getString("email"));
                model.setPhone(resultSet.getString("phone"));
                model.setSex(resultSet.getString("sex"));
                model.setHobby(resultSet.getString("hobby"));
                model.setBirthday(resultSet.getString("birthday"));
                model.setEnable(resultSet.getInt("enable"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return model;
    }

    @Override
    public int update(UserModel userModel) {
        String sql = "UPDATE `user` SET \n" +
                "`phone` = '" + userModel.getPhone() + "', \n" +
                "`sex` = '" + userModel.getSex() + "', \n" +
                "`birthday` = '" + userModel.getBirthday() + "',\n" +
                " `hobby` = '" + userModel.getHobby() + "', \n" +
                " `email` = '" + userModel.getEmail() + "' \n" +
                " WHERE (`id` = '" + userModel.getId() + "')";
        return JDBC.update(sql);
    }

    @Override
    public Integer getCount(UserModel userModel) {
        String sql = "select count(*) from user where  true";
        if (!"".equals(userModel.getUsername()) && userModel.getUsername() != null)
            sql += " and username like '%" + userModel.getUsername() + "%'";
        if (!"".equals(userModel.getPhone()) && userModel.getPhone() != null)
            sql += " and  phone like '%" + userModel.getPhone() + "%'";
        if (!"".equals(userModel.getEmail()) && userModel.getEmail() != null)
            sql += " and  email like '%" + userModel.getEmail() + "%'";
        if (!"全部".equals(userModel.getSex()) && userModel.getSex() != null)
            sql += " and sex = '" + userModel.getSex() + "'";
        if (!"".equals(userModel.getHobby()) && userModel.getHobby() != null) {
            String aa[] = userModel.getHobby().split(",");
            sql += " and (";
            for (int i = 0; i < aa.length; i++) {
                if (i == 0) {
                    sql += "   find_in_set('" + aa[i] + "',hobby)";
                } else {
                    sql += " or  find_in_set('" + aa[i] + "',hobby)";
                }
            }
            sql += ")";
        }
        if (!"".equals(userModel.getStartBirthday()) && "".equals(userModel.getEndBirthday())) {
            sql += " and birthday >='" + userModel.getStartBirthday() + "'";
        }
        if ("".equals(userModel.getStartBirthday()) && !"".equals(userModel.getEndBirthday())) {
            sql += " and birthday <='" + userModel.getEndBirthday() + "'";
        }
        if (!"".equals(userModel.getStartBirthday()) && !"".equals(userModel.getEndBirthday())) {
            sql += " and birthday between '" + userModel.getStartBirthday() + "' and '" + userModel.getEndBirthday() + "'";
        }

        sql += " order by update_time desc ";
//        分页  -- 对象
        ResultSet resultSet = JDBC.query(sql);
        int count = 0;
        try {
            while (resultSet.next()) {
                count = resultSet.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;

    }
}
